package zuoye.SubjectDao.Dao;



import zuoye.SubjectDao.DBUtil.DBUtil;
import zuoye.SubjectDao.Entity.Student;

import java.sql.*;
import java.util.LinkedList;
import java.util.List;


public class StudentDao {
    //  根据学号查询某一学生信息
    public Student searchOne(long id){
        Connection connection = null;
        PreparedStatement ps=null;
        ResultSet rs=null;
        try {
            connection = DBUtil.getConnection();
            ps=connection.prepareStatement("select * from `s_student` where `stuno` = ?;");
            ps.setLong(1,id);
            rs=ps.executeQuery();
            if (rs.next()){
                Student su=new Student();
                su.setStuno(rs.getInt("stuno"));
                su.setSname(rs.getString("sname"));
                su.setSex(rs.getString("sex"));
                su.setTelephone(rs.getString("telephone"));
                su.setQq(rs.getString("qq"));
                su.setWechat(rs.getString("wechat"));
                su.setGradeid(rs.getInt("gradeid"));
                su.setEnterdate(rs.getDate("enterdate"));
                su.setFromcity(rs.getString("fromcity"));
                su.setBirthday(rs.getDate("birthday"));
                su.setAddress(rs.getString("address"));
                su.setPass(rs.getString("pass"));
                return su;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            DBUtil.close(rs,ps,connection);
        }
        return null;
    }

    //  分页查询
    public List<Student> searchPage(int pageNo,int pageSize){
        List<Student>datas=new LinkedList<>();
        Connection connection = null;
        PreparedStatement ps=null;
        ResultSet rs=null;
        try {
            connection = DBUtil.getConnection();
            ps=connection.prepareStatement("select * from s_student order by stuno desc limit ?,?");
            ps.setInt(1,(pageNo-1)*pageSize);
            ps.setInt(2,pageSize);
            rs = ps.executeQuery();
            while (rs.next()){
                Student su=new Student();
                su.setStuno(rs.getInt("stuno"));
                su.setSname(rs.getString("sname"));
                su.setSex(rs.getString("sex"));
                su.setTelephone(rs.getString("telephone"));
                su.setQq(rs.getString("qq"));
                su.setWechat(rs.getString("wechat"));
                su.setGradeid(rs.getInt("gradeid"));
                su.setEnterdate(rs.getDate("enterdate"));
                su.setFromcity(rs.getString("fromcity"));
                su.setBirthday(rs.getDate("birthday"));
                su.setAddress(rs.getString("address"));
                su.setPass(rs.getString("pass"));
                datas.add(su);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            DBUtil.close(rs,ps,connection);
        }
        return datas;
    }

    //模糊查询并判空
    public List<Student> like(String name, String qq, String telephone, int pageNo1, int pageSize1) {
        List<Student>datas=new LinkedList<>();
        Connection connection = DBUtil.getConnection();
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            String sql = "select * from s_student where IFNULL(sname,\"\") LIKE concat('%',?,'%') and IFNULL(qq,\"\") LIKE concat('%',?,'%') and IFNULL(telephone,\"\") LIKE concat('%',?,'%') order by stuno limit ?,?";
            System.out.println(sql);
            ps = connection.prepareStatement(sql);
            ps.setString(1, name);
            ps.setString(2, qq);
            ps.setString(3, telephone);
            ps.setInt(4, pageNo1);
            ps.setInt(5, pageSize1);

            rs = ps.executeQuery();
            while (rs.next()) {
                Student student = new Student();
                student.setStuno(rs.getInt("stuno"));
                student.setSname(rs.getString("sname"));
                student.setSex(rs.getString("sex"));
                student.setTelephone(rs.getString("telephone"));
                student.setQq(rs.getString("qq"));
                student.setWechat(rs.getString("wechat"));
                student.setGradeid(rs.getInt("gradeid"));
                student.setEnterdate(rs.getDate("enterdate"));
                student.setBirthday(rs.getDate("birthday"));
                student.setFromcity(rs.getString("fromcity"));
                student.setAddress(rs.getString("address"));
                student.setPass(rs.getString("pass"));
                datas.add(student);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.close(rs,ps,connection);
        }

        return datas;
    }

}
